package xyz.xiangyushui.util.dataBase;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import xyz.xiangyushui.dao.connectionFactory.DBConnectoryFactory;

public class DBUtil {
	private DBConnectoryFactory factory;
	private Connection conn;
	private PreparedStatement preparedStatement;

	public DBUtil(DBConnectoryFactory factory) {
		super();
		this.factory=factory;
	}

	/**
	 * execute a dataBase command
	 * 
	 * @param cmd
	 */
	public void execute(String... cmd) {
		boolean successful=true;
		conn=factory.getConnection();
		try {
			conn.setAutoCommit(false);
			Statement s = conn.createStatement();
			for (String sql : cmd) {
				s.addBatch(sql);
			}
			s.executeBatch();
			conn.commit();
		} catch (SQLException e) {
			successful=false;
			e.printStackTrace();
		}finally{
			closeAll();
		}
		if(successful){
			for(String c:cmd)
				System.out.println(c);
		}
	}

	/**
	 * insert update delete SQL语句的执行的统一方法
	 * 
	 * @param sql
	 *            SQL语句
	 * @param params
	 *            参数数组，若没有参数则为null
	 * @return 受影响的行数
	 */
	public int executeUpdate(String sql, Object[] params) {
		conn=factory.getConnection();
		int affectedLine = 0;
		try {
			preparedStatement = conn.prepareStatement(sql);
			if (params != null) {
				for (int i = 0; i < params.length; i++) {
					preparedStatement.setObject(i + 1, params[i]);
				}
			}
			affectedLine = preparedStatement.executeUpdate();
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			e.printStackTrace();
		} finally {
			closeAll();
		}
		return affectedLine;
	}
	
	 /** 
     * SQL 查询将查询结果直接放入ResultSet中 
     * @param sql SQL语句 
     * @param params 参数数组，若没有参数则为null 
     * @return 结果集 
     */  
    private ResultSet executeQueryRS(String sql, Object[] params) {  
    	ResultSet resultSet = null;
    	try {  
            conn = factory.getConnection();  
            preparedStatement = conn.prepareStatement(sql);  
            if (params != null) {  
                for (int i = 0; i < params.length; i++) {  
                    preparedStatement.setObject(i + 1, params[i]);  
                }  
            }  
              
            // 执行  
            resultSet = preparedStatement.executeQuery();  
  
        } catch (SQLException e) {  
            System.out.println(e.getMessage());  
        }  
  
        return resultSet;  
    }  
  
    
    
    /** 
     * 获取结果集，并将结果放在List中 
     * @param sql 
     * @return List 
     */  
    public List<Object> excuteQuery(String sql, Object[] params) {  
        ResultSet rs = executeQueryRS(sql, params);  
        ResultSetMetaData rsmd = null;  
        int columnCount = 0;  
        try {  
            rsmd = rs.getMetaData();  
            columnCount = rsmd.getColumnCount();  
        } catch (SQLException e1) {  
            e1.printStackTrace();
        }  
        List<Object> list = new LinkedList<Object>();  
  
        try {  
            while (rs.next()) {  
                Map<String, Object> map = new HashMap<String, Object>();  
                for (int i = 1; i <= columnCount; i++) {  
                    map.put(rsmd.getColumnLabel(i), rs.getObject(i));  
                }  
                list.add(map);  
            }  
        } catch (SQLException e) {  
        	e.printStackTrace();
        } finally {  
            closeAll();  
        }  
        return list;  
    }  

	/**
	 * delete all data in several tables;
	 * @param tableName
	 */
	public void emptyTable(String... tableName) {
		String[] truncateCmd=new String[tableName.length];
		for(int i=0;i<tableName.length;i++){
			 truncateCmd[i]="truncate "+"`"+tableName[i]+"`";
		}
		execute(truncateCmd);
	}
	
	
	/**
	 * get all table in a datebase
	 * @param databaseName
	 * @return
	 */
	public String[] getAllTables(String databaseName){
		String[] tables=null;
		List<Object> list=excuteQuery("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA =?", new Object[]{databaseName});
		if(!list.isEmpty()){
			tables=new String[list.size()];
			for(int i=0;i<list.size();i++){
				@SuppressWarnings("unchecked")
				Map<String,Object> m=(Map<String, Object>) list.get(i);
				tables[i]=(String) m.get("TABLE_NAME");
			}
		}
		return tables;
	}
	
	/** 
     * close all closable resource
     */  
    private void closeAll() {  
        if (preparedStatement != null) {  
            try {  
                preparedStatement.close();  
            } catch (SQLException e) {  
                e.printStackTrace();  
            }  
        }  
        if (conn != null) {  
            try {  
                conn.close();  
            } catch (SQLException e) {  
            	 e.printStackTrace();  
            }  
        }     
    }  

}
